import pymysql
import lxml.html
import re
import time

class DateStatus:

    def __init__(self):
        print("inited!")

    def getList(self):
        conn = pymysql.connect(host='192.168.187.130', user='root', passwd='123456', db='homework')
        cur = conn.cursor()
        uidline = []
        hotline = []
        samline = []
        try:
            sql = "truncate table hotlist"
            cur.execute(sql)
            conn.commit()
            print("table is cleaned!")

            sql = "select uid,sum(hot),count(sid) from sample group by uid order by sum(hot) desc limit 10 "
            cur.execute(sql)
            #print("sql:"+sql)
            hotlist = cur.fetchall()
            #print(str(hotlist))
            for ll in hotlist:
                uidline.append(ll[0])
                hotline.append(int(ll[1]))
                samline.append(ll[2])
            print("hotline:"+str(hotline))
            for i in range(0,10):
                sql = "select uid, uname, company, goodrate, pronum, exnum, lv, income from user where uid = %d" % (uidline[i])
                print("sql:"+sql)
                cur.execute(sql)
                print("completed!")
                result = cur.fetchone()
                print(str(result))
                print("i:"+str(i))
                print("result[0]:" + str(type(result[0]))+" "+str(result[0]))
                print("result[1]:" + str(type(result[1]))+" "+str(result[1]))
                print("result[2]:" + str(type(result[2]))+" "+str(result[2]))
                print("result[3]:" + str(type(result[3]))+" "+str(result[3]))
                print("result[4]:" + str(type(result[4]))+" "+str(result[4]))
                print("result[5]:" + str(type(result[5]))+" "+str(result[5]))
                print("result[6]:" + str(type(result[6]))+" "+str(result[6]))
                print("result[7]:" + str(type(result[7]))+" "+str(result[7]))
                print("line:"+str(type(hotline[i])))
                sql = "insert into hotlist(rank,uid,uname,company,goodrate,pronum,exnum,lv,income,sumhot) values(%d,%d,'%s','%s',%d,%d,%d,%d,%f,%d)" % ((i+1),result[0],result[1],result[2],result[3],result[4],samline[i],result[6],result[7],hotline[i])
                print("sql:"+sql)
                cur.execute(sql)
                conn.commit()
        except:
            print("exception")
        cur.close()
        conn.close()

if __name__ in "__main__":
    cls = DateStatus()
    cls.getList()
